if object_id('BIMonitor.SSISLogReport') is not null
  drop procedure BIMonitor.SSISLogReport
go


create procedure BIMonitor.SSISLogReport
  @Server nvarchar(128) = null,     --null will run all servers in the log
  @EventDetail varchar(30),         --failure,error,task,package,full detail
  @DaysOfHistory int = null         --return all history when null


as


--declare variables
declare @ReportStartDate as datetime,
        @ReportEventLevel as tinyint


--set variables
set @Server = (case when @Server = 'All Servers' then null else @Server end)

set @ReportEventLevel =
 (case @EventDetail
    when 'Failure' then 1           --failures only
    when 'Error' then 2             --failures and errors
    when 'Task' then 3              --package tasks
    when 'Package' then 4           --package start and end
    else 5                          --includes warnings
  end)

set @ReportStartDate =
 (case
    when @DaysOfHistory is null or @DaysOfHistory = 0 then '01/01/2000'
    else dateadd(day,1-@DaysOfHistory,convert(char(8),getdate(),112))
  end)


select
  d.id                                                     as SSISEventLogID,
  s.PackageExecutionGUID,
  isnull(s.PackageName,'UNKNOWN')                          as PackageName,
  s.StartTime                                              as PackageStartDateTime,
  convert(nvarchar(12),s.StartTime,101)                    as PackageStartDate,
  convert(nvarchar(10),s.StartTime,108)                    as PackageStartTime,
  convert(nvarchar(10),s.EndTime,108)                      as PackageEndTime,
  datediff(s,s.StartTime,s.EndTime)                        as PackageExecutionSecondsDuration,
  case d.Event
    when 'OnTaskFailed' then 'Failure'
    when 'OnError' then 'Error'
    when 'OnWarning' then 'Warning'
    else d.Event
  end                                                      as [Event],
  case d.Event
    when 'OnTaskFailed' then 'Failure'
    when 'OnError' then 'Error'
    when 'OnWarning' then 'Warning'
    when 'PackageStart' then 'Package'
    when 'PackageEnd' then 'Package'
    else 'Task'
  end                                                      as EventType,
  case d.Event
    when 'OnTaskFailed' then 'White'
    when 'OnError' then 'White'
    else 'Black'
  end                                                      as SSRSEventFontColor,
  case d.Event
    when 'OnTaskFailed' then 'Bold'
    when 'OnError' then 'Bold'
    when 'OnWarning' then 'Bold'
    else 'Normal'
  end                                                      as SSRSEventFontWeight,
  case d.Event
    when 'OnTaskFailed' then 'Red'
    when 'OnError' then 'Orange'
    when 'OnWarning' then 'Yellow'
    else 'White'
  end                                                      as SSRSEventBackgroundColor,
  d.Computer                                               as [Server],
  d.Operator,
  d.Source                                                 as TaskName,
  convert(nvarchar(12),d.StartTime,101)                    as TaskStartDate,
  convert(nvarchar(12),d.StartTime,108)                    as TaskStartTime,
  convert(nvarchar(10),d.EndTime,108)                      as TaskEndTime,
  datediff(s,d.StartTime,d.EndTime)                        as TaskExecutionSecondsDuration,
  d.datacode                                               as TaskCode,
  d.message                                                as TaskMessage
from
  BIMonitor.SSISLog_Development     as d                              inner join
 (select
    executionid as PackageExecutionGUID,
    max(case when [event] = 'PackageStart' then source else null end) as PackageName,
    min(starttime) as StartTime,
    max(endtime) as EndTime
  from
    BIMonitor.SSISLog_Development
  group by
    executionid)             as s on d.ExecutionID = s.PackageExecutionGUID
where
  s.StartTime >= @ReportStartDate and
 (case d.Event
    when 'OnTaskFailed' then 1
    when 'OnError' then 2
    when 'PackageStart' then 4
    when 'PackageEnd' then 4
    when 'OnWarning' then 5
    else 3
  end) <= @ReportEventLevel and
  d.Computer = isnull(@Server,d.Computer)
  